import sys

from utils.db_utils import db_new_connection


def check_userId_column_type():
    """检查 userId 字段的数据类型"""
    conn = db_new_connection()
    cursor = conn.cursor()

    # 查询 users 表结构，获取每一列的类型
    cursor.execute("PRAGMA table_info(users);")
    columns = cursor.fetchall()

    for column in columns:
        if column[1] == "userId":  # column[1] 是列名
            return column[2]  # column[2] 是数据类型

    return None  # 如果没有找到 userId 字段，返回 None


def check_column_type(table_name, column_name):
    """检查指定表的指定列的数据类型"""
    conn = db_new_connection()
    cursor = conn.cursor()

    # 查询表结构，获取每一列的类型
    cursor.execute(f"PRAGMA table_info({table_name});")
    columns = cursor.fetchall()

    for column in columns:
        if column[1] == column_name:  # column[1] 是列名
            return column[2]  # column[2] 是数据类型

    return None  # 如果没有找到指定字段，返回 None


def migrate_user_table():
    """如果 userId 不是 TEXT 类型，执行迁移"""
    conn = db_new_connection()
    cursor = conn.cursor()

    # 检查 userId 字段的数据类型
    userId_type = check_userId_column_type()
    if userId_type == "TEXT":
        # print("userId 字段已是 TEXT 类型，无需迁移。")
        conn.close()
        return

    # print(f"userId 字段当前是 {userId_type} 类型，开始迁移...")

    # 创建临时表，userId 改为 TEXT 类型
    cursor.execute('''
    CREATE TABLE "users_temp" (
        "pkId" INTEGER PRIMARY KEY AUTOINCREMENT,
        "userId" TEXT NOT NULL,
        "screenName" TEXT NOT NULL,
        "name" TEXT NOT NULL,
        "avatar" TEXT NOT NULL,
        "createTime" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        "platform" TEXT NOT NULL DEFAULT 'X',
        "ishide" INTEGER DEFAULT 0,
        UNIQUE ("userId" ASC)
    );
    ''')

    # 将数据从旧表复制到新表
    cursor.execute('''
    INSERT INTO users_temp (pkId, userId, screenName, name, avatar, createTime, platform, ishide)
    SELECT pkId, userId, screenName, name, avatar, createTime, platform, ishide FROM users;
    ''')

    # 删除旧表
    cursor.execute('DROP TABLE users;')

    # 将临时表重命名为原表名
    cursor.execute('ALTER TABLE users_temp RENAME TO users;')

    # 提交更改并关闭连接
    conn.commit()
    conn.close()

    print("用户表迁移成功：userId 已修改为 TEXT 类型！")


def migrate_articles_table():
    """如果 pkId 不是 TEXT 类型，执行迁移"""
    conn = db_new_connection()
    cursor = conn.cursor()

    # 检查 pkId 字段的数据类型
    pkId_type = check_column_type("articles", "pkId")
    if pkId_type == "TEXT":
        # print("pkId 字段已是 TEXT 类型，无需迁移。")
        conn.close()
        return

    # print(f"pkId 字段当前是 {pkId_type} 类型，开始迁移...")

    # 创建临时表，pkId 改为 TEXT 类型
    cursor.execute('''
    CREATE TABLE "articles_temp" (
        "pkId" TEXT NOT NULL,
        "userId" INTEGER NOT NULL,
        "text" TEXT NOT NULL,
        "media" TEXT,
        "createTime" TIMESTAMP NOT NULL,
        "favoriteCount" INTEGER DEFAULT 0,
        "retweetCount" INTEGER DEFAULT 0,
        "replyCount" INTEGER DEFAULT 0,
        "isDelete" INTEGER NOT NULL DEFAULT 0,
        PRIMARY KEY ("pkId"),
        UNIQUE ("pkId" ASC)
    );
    ''')

    # 将数据从旧表复制到新表
    cursor.execute('''
    INSERT INTO articles_temp (pkId, userId, text, media, createTime, favoriteCount, retweetCount, replyCount, isDelete)
    SELECT pkId, userId, text, media, createTime, favoriteCount, retweetCount, replyCount, isDelete FROM articles;
    ''')

    # 删除旧表
    cursor.execute('DROP TABLE articles;')

    # 将临时表重命名为原表名
    cursor.execute('ALTER TABLE articles_temp RENAME TO articles;')

    # 提交更改并关闭连接
    conn.commit()
    conn.close()

    print("文章表迁移成功：Id 已修改为 TEXT 类型！")


def create_atricles_comments_tables():
    # 连接 SQLite 数据库
    conn = db_new_connection()
    cursor = conn.cursor()

    # 检查表是否存在，如果不存在则创建
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS atricles_comments (
            pkId INTEGER PRIMARY KEY AUTOINCREMENT,
            commentId TEXT,
            content TEXT,
            avatar TEXT,
            name TEXT,
            screenName TEXT,
            createTime TIMESTAMP,
            media TEXT,
            articleId TEXT
        )
        ''')

    print("创建评论表成功！")


def clear_articles_table():
    """清空 articles 表中的所有数据"""
    conn = db_new_connection()
    cursor = conn.cursor()

    # 执行删除操作
    cursor.execute('DELETE FROM articles;')

    # 提交更改并关闭连接
    conn.commit()
    conn.close()

    print("articles 表数据已清空。")

def check_column_exists(table_name, column_name):
    """检查指定表的指定列是否存在"""
    conn = db_new_connection()
    cursor = conn.cursor()

    # 查询表结构，获取每一列的信息
    cursor.execute(f"PRAGMA table_info({table_name});")
    columns = cursor.fetchall()

    for column in columns:
        if column[1] == column_name:  # column[1] 是列名
            conn.close()
            return True

    conn.close()
    return False


def add_articlesId_column():
    """检查并添加 articlesId 字段"""
    conn = db_new_connection()
    cursor = conn.cursor()

    # 检查 articles 表中是否已经有 articlesId 字段
    if not check_column_exists("articles", "articlesId"):
        # 如果没有该字段，添加 articlesId 字段
        cursor.execute('''
        ALTER TABLE articles
        ADD COLUMN articlesId TEXT;
        ''')

        # 提交更改并关闭连接
        conn.commit()
        print("articles 表成功添加了 articlesId 字段。")
    else:
        print("articles 表已经存在 articlesId 字段，无需添加。")

    conn.close()


def run_migrations():
    """执行所有需要的数据库操作"""
    print("开始执行数据库迁移和清理操作...")

    migrate_user_table()
    migrate_articles_table()
    create_atricles_comments_tables()
    # clear_articles_table()
    add_articlesId_column()

    print("所有操作执行完毕。")


if __name__ == "__main__":
    run_migrations()
    sys.exit(0)  # 执行完所有操作后退出
